package dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class AlbumDaoImpl implements AlbumDao{

    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";

    private static final String DATABASE_PROPERTIES_FILE = "jdbc.properties";
    private static final String URL_PROPERTY = "url";
    private static final String USERNAME_PROPERTY = "username";
    private static final String PASSWORD_PROPERTY = "password";

    private static final String SELECT_ALBUMS_STATEMENT = "SELECT * FROM albums";
    private static final String SELECT_ALBUM_BY_ALBUM_ID_STATEMENT = "SELECT * FROM albums WHERE album_id = ?";
    private static final String SELECT_ALBUMS_BY_ARTIST_ID_STATEMENT = "SELECT * FROM albums WHERE album_artist_id = ? ORDER BY album_year";

    private static final String ALBUM_ID_COLUMN = "album_id";
    private static final String ALBUM_NAME_COLUMN = "album_name";
    private static final String ALBUM_YEAR_COLUMN = "album_year";
    private static final String ALBUM_ARTIST_ID_COLUMN = "album_artist_id";
    private static final String ALBUM_DESCRIPTION_COLUMN = "album_description";

    private static final AlbumDao INSTANCE = new AlbumDaoImpl();

    private static String url;
    private static String username;
    private static String password;

    static {
        readDatabaseProperties();
    }

    private AlbumDaoImpl() {

    }

    private static void readDatabaseProperties() {
        InputStream in = null;

        try {
            Properties properties = new Properties();
            in = AlbumDaoImpl.class.getResourceAsStream(DATABASE_PROPERTIES_FILE);

            if (in != null) {
                properties.load(in);
            }

            String url = properties.getProperty(URL_PROPERTY);
            String username = properties.getProperty(USERNAME_PROPERTY);
            String password = properties.getProperty(PASSWORD_PROPERTY);

            AlbumDaoImpl.url = url;
            AlbumDaoImpl.username = username;
            AlbumDaoImpl.password = password;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static AlbumDao getInstance() {
        return INSTANCE;
    }

    @Override
    public List<Album> getAlbums() {
        List<Album> albums = new ArrayList<Album>();

        Connection connection = getConnection();
        Statement statement = null;

        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(SELECT_ALBUMS_STATEMENT);

            while (resultSet.next()) {
                Album album = mapResultSet(resultSet);
                albums.add(album);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return albums;
    }

    @Override
    public Album getAlbumByAlbumId(Long albumId) {
        Album album = null;

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SELECT_ALBUM_BY_ALBUM_ID_STATEMENT);
            preparedStatement.setLong(1, albumId);
            preparedStatement.setMaxRows(1);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                album = mapResultSet(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return album;
    }

    @Override
    public List<Album> getAlbumsByArtistId(Long artistId) {
        List<Album> albums = new ArrayList<Album>();

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SELECT_ALBUMS_BY_ARTIST_ID_STATEMENT);
            preparedStatement.setLong(1, artistId);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Album album = mapResultSet(resultSet);
                albums.add(album);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return albums;
    }

    private Album mapResultSet(ResultSet resultSet) {
        Album album = new Album();

        try {
            album.setId(resultSet.getLong(ALBUM_ID_COLUMN));
            album.setName(resultSet.getString(ALBUM_NAME_COLUMN));
            album.setYear(resultSet.getInt(ALBUM_YEAR_COLUMN));
            album.setArtist_id(resultSet.getLong(ALBUM_ARTIST_ID_COLUMN));
            album.setDescription(resultSet.getString(ALBUM_DESCRIPTION_COLUMN));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return album;
    }

    private Connection getConnection() {
        Connection connection = null;

        try {
            Class.forName(DRIVER_CLASS);
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }
}
